1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3 Imports System.IO
4
5 Public Class frmCustomerRecord1
6
7 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
8 Me.Close()
9 End Sub
10 Public Sub Getdata()
11 Try
12 con = New SqlConnection(cs)
13 con.Open()
14 cmd = New SqlCommand("SELECT RTRIM(ID),RTRIM(CustomerID),RTRIM([Name]),RTRIM(Gender), RTRIM(Address),RTRIM(City),RTRIM(State),RTRIM(ZipCode), RTRIM(ContactNo), RTRIM(EmailID),RTRIM(Remarks) from Customer where CustomerType='Regular' order by name", con)
15 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
16 dgw.Rows.Clear()
17 While (rdr.Read() = True)
18 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
19 End While
20 con.Close()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
26 Getdata()
27 End Sub
28
29 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
30 Me.Close()
31 End Sub
32
33 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
34 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
35 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
36 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
37 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
38 End If
39 Dim b As Brush = SystemBrushes.ControlText
40 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
41
42 End Sub
43
44 Private Sub txtCustomerName_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCustomerName.TextChanged
45 Try
46 con = New SqlConnection(cs)
47 con.Open()
48 cmd = New SqlCommand("SELECT RTRIM(ID),RTRIM(CustomerID),RTRIM([Name]),RTRIM(Gender), RTRIM(Address),RTRIM(City),RTRIM(State),RTRIM(ZipCode), RTRIM(ContactNo), RTRIM(EmailID),RTRIM(Remarks) from Customer where CustomerType='Regular' and name like '%" & txtCustomerName.Text & "%' order by name", con)
49 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
50 dgw.Rows.Clear()
51 While (rdr.Read() = True)
52 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
53 End While
54 con.Close()
55 Catch ex As Exception
56 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
57 End Try
58 End Sub
59
60 Private Sub txtCity_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtCity.TextChanged
61 Try
62 con = New SqlConnection(cs)
63 con.Open()
64 cmd = New SqlCommand("SELECT RTRIM(ID),RTRIM(CustomerID),RTRIM([Name]),RTRIM(Gender), RTRIM(Address),RTRIM(City),RTRIM(State),RTRIM(ZipCode), RTRIM(ContactNo), RTRIM(EmailID),RTRIM(Remarks) from Customer where CustomerType='Regular' and City like '%" & txtCity.Text & "%' order by city", con)
65 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
66 dgw.Rows.Clear()
67 While (rdr.Read() = True)
68 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
69 End While
70 con.Close()
71 Catch ex As Exception
72 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
73 End Try
74 End Sub
75 Sub Reset()
76 txtCustomerName.Text = ""
77 txtContactNo.Text = ""
78 txtCity.Text = ""
79 Getdata()
80 End Sub
81
82 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
83 Reset()
84 End Sub
85
86 Private Sub txtContactNo_TextChanged(sender As System.Object, e As System.EventArgs) Handles txtContactNo.TextChanged
87 Try
88 con = New SqlConnection(cs)
89 con.Open()
90 cmd = New SqlCommand("SELECT RTRIM(ID),RTRIM(CustomerID),RTRIM([Name]),RTRIM(Gender), RTRIM(Address),RTRIM(City),RTRIM(State),RTRIM(ZipCode), RTRIM(ContactNo), RTRIM(EmailID),RTRIM(Remarks) from Customer where CustomerType='Regular' and ContactNo like '%" & txtContactNo.Text & "%' order by city", con)
91 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
92 dgw.Rows.Clear()
93 While (rdr.Read() = True)
94 dgw.Rows.Add(rdr(0), rdr(1), rdr(2), rdr(3), rdr(4), rdr(5), rdr(6), rdr(7), rdr(8), rdr(9), rdr(10))
95 End While
96 con.Close()
97 Catch ex As Exception
98 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
99 End Try
100 End Sub
101
102 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
103 Dim rowsTotal, colsTotal As Short
104 Dim I, j, iC As Short
105 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
106 Dim xlApp As New Excel.Application
107 Try
108 Dim excelBook As Excel.Workbook = xlApp.Workbooks.Add
109 Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
110 xlApp.Visible = True
111
112 rowsTotal = dgw.RowCount
113 colsTotal = dgw.Columns.Count - 1
114 With excelWorksheet
115 .Cells.Select()
116 .Cells.Delete()
117 For iC = 0 To colsTotal
118 .Cells(1, iC + 1).Value = dgw.Columns(iC).HeaderText
119 Next
120 For I = 0 To rowsTotal - 1
121 For j = 0 To colsTotal
122 .Cells(I + 2, j + 1).value = dgw.Rows(I).Cells(j).Value
123 Next j
124 Next I
125 .Rows("1:1").Font.FontStyle = "Bold"
126 .Rows("1:1").Font.Size = 12
127
128 .Cells.Columns.AutoFit()
129 .Cells.Select()
130 .Cells.EntireColumn.AutoFit()
131 .Cells(1, 1).Select()
132 End With
133 Catch ex As Exception
134 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
135 Finally
136 'RELEASE ALLOACTED RESOURCES
137 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
138 xlApp = Nothing
139 End Try
140 End Sub
141 End Class
